package fr.ul.ade.secunde.database;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.provider.BaseColumns;
import fr.ul.ade.secunde.database.AdeWebContract.ErrorColumns;
import fr.ul.ade.secunde.database.AdeWebContract.LessonColumns;
import fr.ul.ade.secunde.database.AdeWebContract.ProjectColumns;
import fr.ul.ade.secunde.database.AdeWebContract.ScheduleColumns;
import fr.ul.ade.secunde.database.AdeWebContract.StudentNumberColumns;

/**
 * Classe responsable de la création, la mise à jour et la suppression de la
 * base de données.
 * 
 * @author Michaël Schnell
 * @since Jul 5, 2013
 * 
 * @version 1.5
 */
public class AdeWebDatabase
		extends SQLiteOpenHelper {

	private static final String DATABASE_NAME = "adeweb.db";//$NON-NLS-1$
	private static final int DATABASE_VERSION = 3;

	/**
	 * Numéro de la base de données avant le passage à la version "Secunde".
	 */
	private static final int DATABASE_PRIME_VERSION = 2;

	interface Tables {
		String SCHEDULE = "schedule"; //$NON-NLS-1$
		String LESSON = "lesson"; //$NON-NLS-1$
		String LESSON_HISTORY = "lesson_history"; //$NON-NLS-1$
		String LESSON_DISPLAY = "lesson_display"; //$NON-NLS-1$
		String PROJECT = "project"; //$NON-NLS-1$
		String STUDENT_NUMBER = "student_number"; //$NON-NLS-1$
		String ERROR = "error_msg"; //$NON-NLS-1$
	}

	interface Views {
		String SCHEDULE = "view_schedule"; //$NON-NLS-1$
		String LESSON = "view_lesson"; //$NON-NLS-1$
		String LESSON_DISPLAY = "view_lesson_display"; //$NON-NLS-1$

		String ID_QUERY = BaseColumns._ID + "=?"; //$NON-NLS-1$
		String LESSON_SCHEDULE_QUERY = LessonColumns.LESSON_SCHEDULE + "=?"; //$NON-NLS-1$
		String LESSON_SCHEDULE_BETWEEN_QUERY = "(%1$s > '%3$s' AND %1$s <= '%4$s') OR (%1$s = '%3$s' AND %2$s >= strftime('%%H:%%M', 'now', 'localtime', '-10 minutes'))"; //$NON-NLS-1$

		String SCHEDULE_ALL_RESOURCES_QUERY = "trim(ifnull(" + ScheduleColumns.SCHEDULE_RESOURCES + ", '') || '|' || ifnull((" + //$NON-NLS-1$ //$NON-NLS-2$
				"SELECT group_concat(" + ScheduleColumns.SCHEDULE_RESOURCES + ", '|') " + //$NON-NLS-1$ //$NON-NLS-2$
				"FROM " + Tables.STUDENT_NUMBER + " sn " + //$NON-NLS-1$ //$NON-NLS-2$
				"WHERE '|' || " + Qualified.SCHEDULE_STUDENT_NUM + " || '|' LIKE '%|' || " + StudentNumberColumns.STUDENT_NUMBER_STUDENT_NUMBER + " || '|%')" + //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
				", ''), '|')"; //$NON-NLS-1$		
		String SCHEDULE_IMPORTANT_CHANGE_QUERY = "(SELECT COUNT(*) FROM " + Tables.LESSON //$NON-NLS-1$
				+ " WHERE " + LessonColumns.LESSON_SCHEDULE + " = " + Qualified.SCHEDULE_ID //$NON-NLS-1$ //$NON-NLS-2$
				+ " AND " + LessonColumns.LESSON_CHANGE + " > 2)"; //$NON-NLS-1$ //$NON-NLS-2$ 

		String LESSON_DISPLAY_DATE_QUERY = "ifnull(" + Qualified.LESSON_DISPLAY_DATE //$NON-NLS-1$
				+ ", " + Qualified.LESSON_VIEW_DATE + ")"; //$NON-NLS-1$ //$NON-NLS-2$
		String LESSON_DISPLAY_END_TIME_QUERY = "ifnull(" + Qualified.LESSON_DISPLAY_END_TIME //$NON-NLS-1$
				+ ", " + Qualified.LESSON_VIEW_END_TIME + ")"; //$NON-NLS-1$ //$NON-NLS-2$
	}

	interface InsertQueries {
		static final String INSERT_LESSON_STMT = "INSERT INTO " + Views.LESSON + " (" //$NON-NLS-1$ //$NON-NLS-2$
				+ LessonColumns.LESSON_TITLE + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_DATE + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_START_TIME + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_END_TIME + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_SCHEDULE + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_CHANGE + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_CREATED + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_ROOMS + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_GROUPS + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_TEACHERS + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_COLOR + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_NOTE + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_GUID + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_DELETED + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_LAST_UPDATE + ")" //$NON-NLS-1$
				+ " VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"; //$NON-NLS-1$
		
		static final String INSERT_LESSON_DISPLAY_STMT = "INSERT INTO " + Views.LESSON_DISPLAY + " (" //$NON-NLS-1$ //$NON-NLS-2$
				+ LessonColumns.LESSON_SCHEDULE + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_ID + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_ORDER + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_DATE + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_END_TIME + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_TYPE + ")" //$NON-NLS-1$
				+ " VALUES (?,?,?,?,?,?)"; //$NON-NLS-1$

		int LESSON_TITLE_INDEX = 1;
		int LESSON_DATE_INDEX = 2;
		int LESSON_START_TIME_INDEX = 3;
		int LESSON_END_TIME_INDEX = 4;
		int LESSON_SCHEDULE_INDEX = 5;
		int LESSON_CHANGE_INDEX = 6;
		int LESSON_CREATED_INDEX = 7;
		int LESSON_ROOMS_INDEX = 8;
		int LESSON_GROUPS_INDEX = 9;
		int LESSON_TEACHERS_INDEX = 10;
		int LESSON_COLOR_INDEX = 11;
		int LESSON_NOTE_INDEX = 12;
		int LESSON_GUID_INDEX = 13;
		int LESSON_DELETED_INDEX = 14;
		int LESSON_LAST_UPDATE_INDEX = 15;
		
		int LESSON_DISPLAY_SCHEDULE_INDEX = 1;
		int LESSON_DISPLAY_ID_INDEX = 2;
		int LESSON_DISPLAY_ORDER_INDEX = 3;
		int LESSON_DISPLAY_DATE_INDEX = 4;
		int LESSON_DISPLAY_END_TIME_INDEX = 5;
		int LESSON_DISPLAY_TYPE_INDEX = 6;
	}

	interface Triggers {
		String SCHEDULE_VIEW_INSERT = "view_schedule_insert"; //$NON-NLS-1$
		String SCHEDULE_VIEW_UPDATE = "view_schedule_update"; //$NON-NLS-1$
		String SCHEDULE_VIEW_DELETE = "view_schedule_delete"; //$NON-NLS-1$

		String LESSON_VIEW_INSERT = "view_lesson_insert"; //$NON-NLS-1$
		String LESSON_DISPLAY_VIEW_INSERT = "view_lesson_display_insert"; //$NON-NLS-1$
		String LESSON_VIEW_UPDATE_LESSON = "view_lesson_update_lesson"; //$NON-NLS-1$
		String LESSON_VIEW_UPDATE_HISTORY = "view_lesson_update_history"; //$NON-NLS-1$
		String LESSON_DISPLAY_VIEW_UPDATE_DISPLAY = "view_lesson_display_update_display"; //$NON-NLS-1$
		String LESSON_DISPLAY_VIEW_DELETE = "view_lesson_display_delete"; //$NON-NLS-1$
	}

	private interface Qualified {
		String SCHEDULE_ID = Tables.SCHEDULE + "." + BaseColumns._ID; //$NON-NLS-1$
		String SCHEDULE_STUDENT_NUM = Tables.SCHEDULE + "." + ScheduleColumns.SCHEDULE_STUDENT_NUM; //$NON-NLS-1$

		String LESSON_ID = Tables.LESSON + "." + BaseColumns._ID; //$NON-NLS-1$
		String LESSON_HISTORY_ID = Tables.LESSON_HISTORY + "." + BaseColumns._ID; //$NON-NLS-1$
		String LESSON_DISPLAY_ID = Tables.LESSON_DISPLAY + "." + BaseColumns._ID; //$NON-NLS-1$
		String LESSON_VIEW_ID = Views.LESSON + "." + BaseColumns._ID; //$NON-NLS-1$
		String LESSON_VIEW_DATE = Views.LESSON + "." + LessonColumns.LESSON_DATE; //$NON-NLS-1$
		String LESSON_DISPLAY_DATE = Tables.LESSON_DISPLAY + "." + LessonColumns.LESSON_DATE; //$NON-NLS-1$
		String LESSON_VIEW_END_TIME = Views.LESSON + "." + LessonColumns.LESSON_END_TIME; //$NON-NLS-1$
		String LESSON_DISPLAY_END_TIME = Tables.LESSON_DISPLAY + "." + LessonColumns.LESSON_END_TIME; //$NON-NLS-1$
		String LESSON_DISPLAY_SCHEDULE = Tables.LESSON_DISPLAY + "." + LessonColumns.LESSON_SCHEDULE; //$NON-NLS-1$

		String PROJECT_ID = Tables.PROJECT + "." + BaseColumns._ID; //$NON-NLS-1$
	}

	public AdeWebDatabase(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		createTables(db);
		createViews(db);
		createTriggers(db);
	}

	private static void createTables(SQLiteDatabase db) {
		db.execSQL("CREATE TABLE " + Tables.SCHEDULE + " (" //$NON-NLS-1$ //$NON-NLS-2$
				+ BaseColumns._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," //$NON-NLS-1$
				+ ScheduleColumns.SCHEDULE_NAME + " VARCHAR(100) NOT NULL," //$NON-NLS-1$
				+ ScheduleColumns.SCHEDULE_DESCRIPTION + " VARCHAR(250) NULL," //$NON-NLS-1$
				+ ScheduleColumns.SCHEDULE_STATE + " INTEGER NOT NULL," //$NON-NLS-1$
				+ ScheduleColumns.SCHEDULE_LAST_UPDATE + " DATETIME NULL," //$NON-NLS-1$
				+ ScheduleColumns.SCHEDULE_RESOURCES + " VARCHAR(250) NULL," //$NON-NLS-1$
				+ ScheduleColumns.SCHEDULE_STUDENT_NUM + " VARCHAR(250) NULL," //$NON-NLS-1$
				+ ScheduleColumns.SCHEDULE_PERIOD + " INTEGER NOT NULL," //$NON-NLS-1$
				+ ScheduleColumns.SCHEDULE_PROJECT + " INTEGER NOT NULL," //$NON-NLS-1$
				+ ScheduleColumns.SCHEDULE_NOTIFY_UPDATE + " INTEGER NOT NULL," //$NON-NLS-1$
				+ ScheduleColumns.SCHEDULE_CREATED + " DATETIME DEFAULT (datetime('now', 'localtime')))"); //$NON-NLS-1$

		db.execSQL("CREATE TABLE " + Tables.LESSON + " (" //$NON-NLS-1$//$NON-NLS-2$
				+ BaseColumns._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," //$NON-NLS-1$
				+ LessonColumns.LESSON_TITLE + " VARCHAR(100) NOT NULL," //$NON-NLS-1$
				+ LessonColumns.LESSON_DATE + " DATE NOT NULL," //$NON-NLS-1$
				+ LessonColumns.LESSON_START_TIME + " TIME NOT NULL," //$NON-NLS-1$
				+ LessonColumns.LESSON_END_TIME + " TIME NOT NULL," //$NON-NLS-1$
				+ LessonColumns.LESSON_SCHEDULE + " INTEGER NOT NULL," //$NON-NLS-1$
				+ LessonColumns.LESSON_CHANGE + " INTEGER NOT NULL," //$NON-NLS-1$
				+ LessonColumns.LESSON_CREATED + " DATETIME DEFAULT (datetime('now', 'localtime'))," //$NON-NLS-1$
				+ LessonColumns.LESSON_ROOMS + " VARCHAR(100) NULL," //$NON-NLS-1$
				+ LessonColumns.LESSON_GROUPS + " VARCHAR(100) NULL," //$NON-NLS-1$
				+ LessonColumns.LESSON_TEACHERS + " VARCHAR(100) NULL," //$NON-NLS-1$
				+ LessonColumns.LESSON_COLOR + " VARCHAR(10) NOT NULL," //$NON-NLS-1$
				+ LessonColumns.LESSON_NOTE + " TEXT NULL," //$NON-NLS-1$
				+ LessonColumns.LESSON_GUID + " VARCHAR(10) NOT NULL," //$NON-NLS-1$
				+ LessonColumns.LESSON_DELETED + " INTEGER NOT NULL," //$NON-NLS-1$
				+ LessonColumns.LESSON_LAST_UPDATE + " DATETIME NOT NULL)"); //$NON-NLS-1$

		db.execSQL("CREATE TABLE " + Tables.LESSON_HISTORY + " (" //$NON-NLS-1$//$NON-NLS-2$
				+ BaseColumns._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," //$NON-NLS-1$
				+ LessonColumns.LESSON_OLD_DATE + " DATE NOT NULL," //$NON-NLS-1$
				+ LessonColumns.LESSON_OLD_START_TIME + " TIME NOT NULL," //$NON-NLS-1$
				+ LessonColumns.LESSON_OLD_END_TIME + " TIME NOT NULL," //$NON-NLS-1$
				+ LessonColumns.LESSON_OLD_ROOMS + " VARCHAR(100) NULL)"); //$NON-NLS-1$

		db.execSQL("CREATE TABLE " + Tables.LESSON_DISPLAY + " (" //$NON-NLS-1$//$NON-NLS-2$
				+ BaseColumns._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," //$NON-NLS-1$
				+ LessonColumns.LESSON_ORDER + " INTEGER NOT NULL," //$NON-NLS-1$
				+ LessonColumns.LESSON_DATE + " DATE NULL," //$NON-NLS-1$
				+ LessonColumns.LESSON_END_TIME + " TIME NULL," //$NON-NLS-1$
				+ LessonColumns.LESSON_SCHEDULE + " INTEGER NOT NULL," //$NON-NLS-1$
				+ LessonColumns.LESSON_ID + " INTEGER NULL," //$NON-NLS-1$
				+ LessonColumns.LESSON_TYPE + " INTEGER NOT NULL)"); //$NON-NLS-1$

		db.execSQL("CREATE TABLE " + Tables.PROJECT + " (" //$NON-NLS-1$//$NON-NLS-2$
				+ BaseColumns._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," //$NON-NLS-1$
				+ ProjectColumns.PROJECT_ADEWEB_ID + " INTEGER NOT NULL," //$NON-NLS-1$
				+ ProjectColumns.PROJECT_NAME + " VARCHAR(100) NOT NULL," //$NON-NLS-1$
				+ ProjectColumns.PROJECT_LOADED + " INTEGER NOT NULL)"); //$NON-NLS-1$

		db.execSQL("CREATE TABLE " + Tables.STUDENT_NUMBER + " (" //$NON-NLS-1$//$NON-NLS-2$
				+ BaseColumns._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," //$NON-NLS-1$
				+ StudentNumberColumns.STUDENT_NUMBER_CREATED + " DATETIME DEFAULT (datetime('now', 'localtime'))," //$NON-NLS-1$
				+ StudentNumberColumns.STUDENT_NUMBER_STUDENT_NUMBER + " VARCHAR(15) NOT NULL," //$NON-NLS-1$
				+ StudentNumberColumns.STUDENT_NUMBER_RESOURCES + " VARCHAR(250) NOT NULL)"); //$NON-NLS-1$

		db.execSQL("CREATE TABLE " + Tables.ERROR + " (" //$NON-NLS-1$//$NON-NLS-2$
				+ BaseColumns._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," //$NON-NLS-1$
				+ ErrorColumns.ERROR_SCHEDULE + " INTEGER NULL," //$NON-NLS-1$
				+ ErrorColumns.ERROR_DATE + " DATETIME DEFAULT (datetime('now', 'localtime'))," //$NON-NLS-1$
				+ ErrorColumns.ERROR_MESSAGE + " VARCHAR(500) NOT NULL," //$NON-NLS-1$
				+ ErrorColumns.ERROR_NUMBER + " INTEGER NOT NULL," //$NON-NLS-1$
				+ ErrorColumns.ERROR_TYPE + " INTEGER NOT NULL)"); //$NON-NLS-1$
	}

	private static void createViews(SQLiteDatabase db) {
		db.execSQL("CREATE VIEW " + Views.SCHEDULE + " AS SELECT " //$NON-NLS-1$//$NON-NLS-2$
				+ Qualified.SCHEDULE_ID + "," //$NON-NLS-1$
				+ ScheduleColumns.SCHEDULE_NAME + "," //$NON-NLS-1$
				+ ScheduleColumns.SCHEDULE_DESCRIPTION + "," //$NON-NLS-1$
				+ ScheduleColumns.SCHEDULE_STATE + "," //$NON-NLS-1$
				+ ScheduleColumns.SCHEDULE_LAST_UPDATE + "," //$NON-NLS-1$
				+ ScheduleColumns.SCHEDULE_RESOURCES + "," //$NON-NLS-1$
				+ ScheduleColumns.SCHEDULE_STUDENT_NUM + "," //$NON-NLS-1$
				+ Views.SCHEDULE_ALL_RESOURCES_QUERY + " AS " + ScheduleColumns.SCHEDULE_ALL_RESOURCES + "," //$NON-NLS-1$//$NON-NLS-2$
				+ ScheduleColumns.SCHEDULE_PERIOD + "," //$NON-NLS-1$
				+ ScheduleColumns.SCHEDULE_PROJECT + "," //$NON-NLS-1$
				+ ScheduleColumns.SCHEDULE_ADE_PROJECT_ID + "," //$NON-NLS-1$
				+ ScheduleColumns.SCHEDULE_PROJECT_NAME + "," //$NON-NLS-1$
				+ ScheduleColumns.SCHEDULE_NOTIFY_UPDATE + "," //$NON-NLS-1$
				+ Views.SCHEDULE_IMPORTANT_CHANGE_QUERY + " AS " + ScheduleColumns.SCHEDULE_IMPORTANT_CHANGE + "," //$NON-NLS-1$ //$NON-NLS-2$
				+ ScheduleColumns.SCHEDULE_CREATED + " FROM " + Tables.SCHEDULE //$NON-NLS-1$
				+ " LEFT JOIN " + Tables.PROJECT + " ON " + ScheduleColumns.SCHEDULE_PROJECT + " = " + Qualified.PROJECT_ID); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$

		db.execSQL("CREATE VIEW " + Views.LESSON + " AS SELECT " //$NON-NLS-1$//$NON-NLS-2$
				+ Qualified.LESSON_ID + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_TITLE + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_DATE + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_START_TIME + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_END_TIME + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_SCHEDULE + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_CHANGE + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_CREATED + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_ROOMS + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_GROUPS + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_TEACHERS + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_COLOR + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_NOTE + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_GUID + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_DELETED + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_LAST_UPDATE + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_OLD_DATE + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_OLD_START_TIME + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_OLD_END_TIME + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_OLD_ROOMS + " FROM " + Tables.LESSON //$NON-NLS-1$
				+ " LEFT JOIN " + Tables.LESSON_HISTORY + " ON " + Qualified.LESSON_ID + " = " + Qualified.LESSON_HISTORY_ID); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$

		db.execSQL("CREATE VIEW " + Views.LESSON_DISPLAY + " AS SELECT " //$NON-NLS-1$//$NON-NLS-2$
				+ Qualified.LESSON_DISPLAY_ID + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_TITLE + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_START_TIME + "," //$NON-NLS-1$
				+ Qualified.LESSON_DISPLAY_SCHEDULE + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_CHANGE + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_CREATED + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_ROOMS + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_GROUPS + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_TEACHERS + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_COLOR + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_NOTE + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_GUID + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_DELETED + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_LAST_UPDATE + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_OLD_DATE + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_OLD_START_TIME + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_OLD_END_TIME + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_OLD_ROOMS + "," //$NON-NLS-1$
				+ Views.LESSON_DISPLAY_DATE_QUERY + " AS " + LessonColumns.LESSON_DATE + "," //$NON-NLS-1$ //$NON-NLS-2$
				+ Views.LESSON_DISPLAY_END_TIME_QUERY + " AS " + LessonColumns.LESSON_END_TIME + "," //$NON-NLS-1$ //$NON-NLS-2$
				+ LessonColumns.LESSON_ID + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_ORDER + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_TYPE + " FROM " + Tables.LESSON_DISPLAY //$NON-NLS-1$
				+ " LEFT JOIN " + Views.LESSON + " ON " + LessonColumns.LESSON_ID + " = " + Qualified.LESSON_VIEW_ID); //$NON-NLS-1$ //$NON-NLS-2$//$NON-NLS-3$
	}

	private static void createTriggers(SQLiteDatabase db) {
		db.execSQL("CREATE TRIGGER " + Triggers.SCHEDULE_VIEW_INSERT //$NON-NLS-1$
				+ " INSTEAD OF INSERT ON " + Views.SCHEDULE //$NON-NLS-1$
				+ " FOR EACH ROW BEGIN " //$NON-NLS-1$
				+ " INSERT INTO " + Tables.SCHEDULE + " (" //$NON-NLS-1$ //$NON-NLS-2$
				+ ScheduleColumns.SCHEDULE_NAME + "," //$NON-NLS-1$
				+ ScheduleColumns.SCHEDULE_DESCRIPTION + "," //$NON-NLS-1$
				+ ScheduleColumns.SCHEDULE_STATE + "," //$NON-NLS-1$
				+ ScheduleColumns.SCHEDULE_LAST_UPDATE + "," //$NON-NLS-1$
				+ ScheduleColumns.SCHEDULE_RESOURCES + "," //$NON-NLS-1$
				+ ScheduleColumns.SCHEDULE_STUDENT_NUM + "," //$NON-NLS-1$
				+ ScheduleColumns.SCHEDULE_PERIOD + "," //$NON-NLS-1$
				+ ScheduleColumns.SCHEDULE_PROJECT + "," //$NON-NLS-1$
				+ ScheduleColumns.SCHEDULE_NOTIFY_UPDATE + ")  VALUES (" //$NON-NLS-1$
				+ "new." + ScheduleColumns.SCHEDULE_NAME + "," //$NON-NLS-1$ //$NON-NLS-2$
				+ "new." + ScheduleColumns.SCHEDULE_DESCRIPTION + "," //$NON-NLS-1$ //$NON-NLS-2$
				+ "new." + ScheduleColumns.SCHEDULE_STATE + "," //$NON-NLS-1$ //$NON-NLS-2$
				+ "new." + ScheduleColumns.SCHEDULE_LAST_UPDATE + "," //$NON-NLS-1$ //$NON-NLS-2$
				+ "new." + ScheduleColumns.SCHEDULE_RESOURCES + "," //$NON-NLS-1$ //$NON-NLS-2$
				+ "new." + ScheduleColumns.SCHEDULE_STUDENT_NUM + "," //$NON-NLS-1$ //$NON-NLS-2$
				+ "new." + ScheduleColumns.SCHEDULE_PERIOD + "," //$NON-NLS-1$ //$NON-NLS-2$
				+ "new." + ScheduleColumns.SCHEDULE_PROJECT + "," //$NON-NLS-1$ //$NON-NLS-2$
				+ "new." + ScheduleColumns.SCHEDULE_NOTIFY_UPDATE + ");" //$NON-NLS-1$ //$NON-NLS-2$
				+ " END"); //$NON-NLS-1$

		db.execSQL("CREATE TRIGGER " + Triggers.SCHEDULE_VIEW_UPDATE //$NON-NLS-1$
				+ " INSTEAD OF UPDATE OF " //$NON-NLS-1$
				+ ScheduleColumns.SCHEDULE_NAME + "," //$NON-NLS-1$
				+ ScheduleColumns.SCHEDULE_DESCRIPTION + "," //$NON-NLS-1$
				+ ScheduleColumns.SCHEDULE_STATE + "," //$NON-NLS-1$
				+ ScheduleColumns.SCHEDULE_LAST_UPDATE + "," //$NON-NLS-1$
				+ ScheduleColumns.SCHEDULE_RESOURCES + "," //$NON-NLS-1$
				+ ScheduleColumns.SCHEDULE_STUDENT_NUM + "," //$NON-NLS-1$
				+ ScheduleColumns.SCHEDULE_PERIOD + "," //$NON-NLS-1$
				+ ScheduleColumns.SCHEDULE_PROJECT + "," //$NON-NLS-1$
				+ ScheduleColumns.SCHEDULE_CREATED + "," //$NON-NLS-1$
				+ ScheduleColumns.SCHEDULE_NOTIFY_UPDATE + " ON " + Views.SCHEDULE //$NON-NLS-1$
				+ " FOR EACH ROW BEGIN " //$NON-NLS-1$
				+ " UPDATE " + Tables.SCHEDULE + " SET " //$NON-NLS-1$ //$NON-NLS-2$
				+ ScheduleColumns.SCHEDULE_NAME + " = new." + ScheduleColumns.SCHEDULE_NAME + "," //$NON-NLS-1$ //$NON-NLS-2$
				+ ScheduleColumns.SCHEDULE_DESCRIPTION + " = new." + ScheduleColumns.SCHEDULE_DESCRIPTION + "," //$NON-NLS-1$ //$NON-NLS-2$
				+ ScheduleColumns.SCHEDULE_STATE + " = new." + ScheduleColumns.SCHEDULE_STATE + "," //$NON-NLS-1$ //$NON-NLS-2$
				+ ScheduleColumns.SCHEDULE_LAST_UPDATE + " = new." + ScheduleColumns.SCHEDULE_LAST_UPDATE + "," //$NON-NLS-1$ //$NON-NLS-2$
				+ ScheduleColumns.SCHEDULE_RESOURCES + " = new." + ScheduleColumns.SCHEDULE_RESOURCES + "," //$NON-NLS-1$ //$NON-NLS-2$
				+ ScheduleColumns.SCHEDULE_STUDENT_NUM + " = new." + ScheduleColumns.SCHEDULE_STUDENT_NUM + "," //$NON-NLS-1$ //$NON-NLS-2$
				+ ScheduleColumns.SCHEDULE_PERIOD + " = new." + ScheduleColumns.SCHEDULE_PERIOD + "," //$NON-NLS-1$ //$NON-NLS-2$
				+ ScheduleColumns.SCHEDULE_PROJECT + " = new." + ScheduleColumns.SCHEDULE_PROJECT + "," //$NON-NLS-1$ //$NON-NLS-2$
				+ ScheduleColumns.SCHEDULE_CREATED + " = new." + ScheduleColumns.SCHEDULE_CREATED + "," //$NON-NLS-1$ //$NON-NLS-2$
				+ ScheduleColumns.SCHEDULE_NOTIFY_UPDATE + " = new." + ScheduleColumns.SCHEDULE_NOTIFY_UPDATE //$NON-NLS-1$
				+ " WHERE " + BaseColumns._ID + " = new." + BaseColumns._ID + ";" //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
				+ " END"); //$NON-NLS-1$

		db.execSQL("CREATE TRIGGER " + Triggers.SCHEDULE_VIEW_DELETE //$NON-NLS-1$
				+ " INSTEAD OF DELETE ON " + Views.SCHEDULE //$NON-NLS-1$
				+ " FOR EACH ROW BEGIN " //$NON-NLS-1$
				+ " DELETE FROM " + Tables.SCHEDULE //$NON-NLS-1$
				+ " WHERE " + BaseColumns._ID + " = old." + BaseColumns._ID + ";" //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
				+ " DELETE FROM " + Views.LESSON_DISPLAY //$NON-NLS-1$
				+ " WHERE " + LessonColumns.LESSON_SCHEDULE + " = old." + BaseColumns._ID + ";" //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
				+ " END"); //$NON-NLS-1$

		db.execSQL("CREATE TRIGGER " + Triggers.LESSON_VIEW_INSERT //$NON-NLS-1$
				+ " INSTEAD OF INSERT ON " + Views.LESSON //$NON-NLS-1$
				+ " FOR EACH ROW BEGIN " //$NON-NLS-1$
				+ " INSERT INTO " + Tables.LESSON + " (" //$NON-NLS-1$ //$NON-NLS-2$
				+ LessonColumns.LESSON_TITLE + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_DATE + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_START_TIME + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_END_TIME + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_SCHEDULE + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_CHANGE + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_CREATED + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_ROOMS + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_GROUPS + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_TEACHERS + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_COLOR + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_NOTE + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_GUID + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_DELETED + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_LAST_UPDATE + ")  VALUES (" //$NON-NLS-1$
				+ "new." + LessonColumns.LESSON_TITLE + "," //$NON-NLS-1$ //$NON-NLS-2$
				+ "new." + LessonColumns.LESSON_DATE + "," //$NON-NLS-1$ //$NON-NLS-2$
				+ "new." + LessonColumns.LESSON_START_TIME + "," //$NON-NLS-1$ //$NON-NLS-2$
				+ "new." + LessonColumns.LESSON_END_TIME + "," //$NON-NLS-1$ //$NON-NLS-2$
				+ "new." + LessonColumns.LESSON_SCHEDULE + "," //$NON-NLS-1$ //$NON-NLS-2$
				+ "new." + LessonColumns.LESSON_CHANGE + "," //$NON-NLS-1$ //$NON-NLS-2$
				+ "new." + LessonColumns.LESSON_CREATED + "," //$NON-NLS-1$ //$NON-NLS-2$
				+ "new." + LessonColumns.LESSON_ROOMS + "," //$NON-NLS-1$ //$NON-NLS-2$
				+ "new." + LessonColumns.LESSON_GROUPS + "," //$NON-NLS-1$ //$NON-NLS-2$
				+ "new." + LessonColumns.LESSON_TEACHERS + "," //$NON-NLS-1$ //$NON-NLS-2$
				+ "new." + LessonColumns.LESSON_COLOR + "," //$NON-NLS-1$ //$NON-NLS-2$
				+ "new." + LessonColumns.LESSON_NOTE + "," //$NON-NLS-1$ //$NON-NLS-2$
				+ "new." + LessonColumns.LESSON_GUID + "," //$NON-NLS-1$ //$NON-NLS-2$
				+ "new." + LessonColumns.LESSON_DELETED  + "," //$NON-NLS-1$ //$NON-NLS-2$
				+ "new." + LessonColumns.LESSON_LAST_UPDATE + ");" //$NON-NLS-1$ //$NON-NLS-2$
				+ " END"); //$NON-NLS-1$

		db.execSQL("CREATE TRIGGER " + Triggers.LESSON_DISPLAY_VIEW_INSERT //$NON-NLS-1$
				+ " INSTEAD OF INSERT ON " + Views.LESSON_DISPLAY //$NON-NLS-1$
				+ " FOR EACH ROW BEGIN " //$NON-NLS-1$
				+ " INSERT INTO " + Tables.LESSON_DISPLAY + " (" //$NON-NLS-1$ //$NON-NLS-2$
				+ LessonColumns.LESSON_SCHEDULE + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_ID + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_ORDER + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_DATE + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_END_TIME + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_TYPE + ") VALUES (" //$NON-NLS-1$
				+ "new." + LessonColumns.LESSON_SCHEDULE + "," //$NON-NLS-1$ //$NON-NLS-2$
				+ "new." + LessonColumns.LESSON_ID + "," //$NON-NLS-1$ //$NON-NLS-2$
				+ "new." + LessonColumns.LESSON_ORDER + "," //$NON-NLS-1$ //$NON-NLS-2$
				+ "new." + LessonColumns.LESSON_DATE + "," //$NON-NLS-1$ //$NON-NLS-2$
				+ "new." + LessonColumns.LESSON_END_TIME + "," //$NON-NLS-1$ //$NON-NLS-2$
				+ "new." + LessonColumns.LESSON_TYPE //$NON-NLS-1$
				+ ");" //$NON-NLS-1$
				+ " END"); //$NON-NLS-1$

		db.execSQL("CREATE TRIGGER " + Triggers.LESSON_VIEW_UPDATE_LESSON //$NON-NLS-1$
				+ " INSTEAD OF UPDATE OF " //$NON-NLS-1$
				+ LessonColumns.LESSON_TITLE + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_DATE + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_START_TIME + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_END_TIME + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_SCHEDULE + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_CHANGE + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_CREATED + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_ROOMS + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_GROUPS + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_TEACHERS + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_COLOR + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_NOTE + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_GUID + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_DELETED + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_LAST_UPDATE + " ON " + Views.LESSON //$NON-NLS-1$
				+ " FOR EACH ROW BEGIN " //$NON-NLS-1$
				+ " UPDATE " + Tables.LESSON + " SET " //$NON-NLS-1$ //$NON-NLS-2$
				+ LessonColumns.LESSON_TITLE + " = new." + LessonColumns.LESSON_TITLE + "," //$NON-NLS-1$ //$NON-NLS-2$
				+ LessonColumns.LESSON_DATE + " = new." + LessonColumns.LESSON_DATE + "," //$NON-NLS-1$ //$NON-NLS-2$
				+ LessonColumns.LESSON_START_TIME + " = new." + LessonColumns.LESSON_START_TIME + "," //$NON-NLS-1$ //$NON-NLS-2$
				+ LessonColumns.LESSON_END_TIME + " = new." + LessonColumns.LESSON_END_TIME + "," //$NON-NLS-1$ //$NON-NLS-2$
				+ LessonColumns.LESSON_SCHEDULE + " = new." + LessonColumns.LESSON_SCHEDULE + "," //$NON-NLS-1$ //$NON-NLS-2$
				+ LessonColumns.LESSON_CHANGE + " = new." + LessonColumns.LESSON_CHANGE + "," //$NON-NLS-1$ //$NON-NLS-2$
				+ LessonColumns.LESSON_CREATED + " = new." + LessonColumns.LESSON_CREATED + "," //$NON-NLS-1$ //$NON-NLS-2$
				+ LessonColumns.LESSON_ROOMS + " = new." + LessonColumns.LESSON_ROOMS + "," //$NON-NLS-1$ //$NON-NLS-2$
				+ LessonColumns.LESSON_GROUPS + " = new." + LessonColumns.LESSON_GROUPS + "," //$NON-NLS-1$ //$NON-NLS-2$
				+ LessonColumns.LESSON_TEACHERS + " = new." + LessonColumns.LESSON_TEACHERS + "," //$NON-NLS-1$ //$NON-NLS-2$
				+ LessonColumns.LESSON_COLOR + " = new." + LessonColumns.LESSON_COLOR + "," //$NON-NLS-1$ //$NON-NLS-2$
				+ LessonColumns.LESSON_NOTE + " = new." + LessonColumns.LESSON_NOTE + "," //$NON-NLS-1$ //$NON-NLS-2$
				+ LessonColumns.LESSON_GUID + " = new." + LessonColumns.LESSON_GUID + "," //$NON-NLS-1$ //$NON-NLS-2$
				+ LessonColumns.LESSON_DELETED + " = new." + LessonColumns.LESSON_DELETED + "," //$NON-NLS-1$ //$NON-NLS-2$
				+ LessonColumns.LESSON_LAST_UPDATE + " = new." + LessonColumns.LESSON_LAST_UPDATE //$NON-NLS-1$
				+ " WHERE " + BaseColumns._ID + " = new." + BaseColumns._ID + ";" //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
				+ " END"); //$NON-NLS-1$

		db.execSQL("CREATE TRIGGER " + Triggers.LESSON_VIEW_UPDATE_HISTORY //$NON-NLS-1$
				+ " INSTEAD OF UPDATE OF " //$NON-NLS-1$
				+ LessonColumns.LESSON_OLD_DATE + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_OLD_START_TIME + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_OLD_END_TIME + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_OLD_ROOMS + " ON " + Views.LESSON //$NON-NLS-1$
				+ " FOR EACH ROW BEGIN " //$NON-NLS-1$
				+ " INSERT OR REPLACE INTO " + Tables.LESSON_HISTORY + " (" //$NON-NLS-1$ //$NON-NLS-2$
				+ BaseColumns._ID + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_OLD_DATE + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_OLD_START_TIME + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_OLD_END_TIME + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_OLD_ROOMS + ") VALUES (" //$NON-NLS-1$
				+ "new." + BaseColumns._ID + "," //$NON-NLS-1$ //$NON-NLS-2$
				+ "new." + LessonColumns.LESSON_OLD_DATE + "," //$NON-NLS-1$ //$NON-NLS-2$
				+ "new." + LessonColumns.LESSON_OLD_START_TIME + "," //$NON-NLS-1$ //$NON-NLS-2$
				+ "new." + LessonColumns.LESSON_OLD_END_TIME + "," //$NON-NLS-1$ //$NON-NLS-2$
				+ "new." + LessonColumns.LESSON_OLD_ROOMS //$NON-NLS-1$
				+ ");" //$NON-NLS-1$
				+ " END"); //$NON-NLS-1$

		db.execSQL("CREATE TRIGGER " + Triggers.LESSON_DISPLAY_VIEW_UPDATE_DISPLAY //$NON-NLS-1$
				+ " INSTEAD OF UPDATE OF " //$NON-NLS-1$
				+ LessonColumns.LESSON_ORDER + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_DATE + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_END_TIME + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_SCHEDULE + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_ID + "," //$NON-NLS-1$
				+ LessonColumns.LESSON_TYPE + " ON " + Views.LESSON_DISPLAY //$NON-NLS-1$
				+ " FOR EACH ROW BEGIN " //$NON-NLS-1$
				+ " UPDATE " + Tables.LESSON_DISPLAY + " SET " //$NON-NLS-1$ //$NON-NLS-2$
				+ LessonColumns.LESSON_SCHEDULE + " = new." + LessonColumns.LESSON_SCHEDULE + "," //$NON-NLS-1$ //$NON-NLS-2$
				+ LessonColumns.LESSON_ID + " = new." + LessonColumns.LESSON_ID + "," //$NON-NLS-1$ //$NON-NLS-2$
				+ LessonColumns.LESSON_ORDER + " = new." + LessonColumns.LESSON_ORDER + "," //$NON-NLS-1$ //$NON-NLS-2$
				+ LessonColumns.LESSON_DATE + " = new." + LessonColumns.LESSON_DATE + "," //$NON-NLS-1$ //$NON-NLS-2$
				+ LessonColumns.LESSON_END_TIME + " = new." + LessonColumns.LESSON_END_TIME + "," //$NON-NLS-1$ //$NON-NLS-2$
				+ LessonColumns.LESSON_TYPE + " = new." + LessonColumns.LESSON_TYPE //$NON-NLS-1$
				+ " WHERE " + BaseColumns._ID + " = new." + BaseColumns._ID + ";" //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
				+ " END"); //$NON-NLS-1$

		db.execSQL("CREATE TRIGGER " + Triggers.LESSON_DISPLAY_VIEW_DELETE //$NON-NLS-1$
				+ " INSTEAD OF DELETE ON " + Views.LESSON_DISPLAY //$NON-NLS-1$
				+ " FOR EACH ROW BEGIN " //$NON-NLS-1$
				+ " DELETE FROM " + Tables.LESSON_DISPLAY //$NON-NLS-1$
				+ " WHERE " + BaseColumns._ID + " = old." + BaseColumns._ID + ";" //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
				+ " DELETE FROM " + Tables.LESSON_HISTORY //$NON-NLS-1$
				+ " WHERE " + BaseColumns._ID + " = old." + LessonColumns.LESSON_ID + ";" //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
				+ " DELETE FROM " + Tables.LESSON //$NON-NLS-1$
				+ " WHERE " + BaseColumns._ID + " = old." + LessonColumns.LESSON_ID + ";" //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
				+ " END"); //$NON-NLS-1$
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		switch (oldVersion) {
		case DATABASE_PRIME_VERSION:
		default:
		}
	}

}// class AdeWebDatabase